#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
精准Excel合并单元格识别模块
提供高效、准确的Excel合并单元格识别功能
"""

import logging
import os
from typing import Dict, List, Tuple, Optional, Any, Union
import pandas as pd
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from pathlib import Path

from src.utils.logger import get_logger

logger = get_logger(__name__)

class PreciseMergedCellDetector:
    """精准合并单元格检测器类"""
    
    def __init__(self):
        """初始化检测器"""
        logger.info("初始化精准合并单元格检测器")
    
    def get_merged_cells_info(self, file_path: str, sheet_name: Union[str, int] = 0) -> Optional[List[Dict[str, Any]]]:
        """
        获取Excel文件中指定工作表的合并单元格信息
        
        Args:
            file_path (str): Excel文件路径
            sheet_name (str or int, optional): 工作表名称或索引，默认为0
            
        Returns:
            List[Dict]: 合并单元格信息列表，每个字典包含：
                - min_col: 起始列号（从1开始）
                - min_row: 起始行号（从1开始）
                - max_col: 结束列号（从1开始）
                - max_row: 结束行号（从1开始）
                - value: 合并单元格的值（左上角单元格的值）
                - range: 合并单元格的坐标范围
                - spans_multiple_rows: 是否跨越多行
        """
        try:
            # 检查文件是否存在
            if not os.path.exists(file_path):
                logger.error(f"文件不存在: {file_path}")
                return None
            
            # 检查文件格式
            file_ext = Path(file_path).suffix.lower()
            if file_ext != '.xlsx':
                logger.error(f"仅支持.xlsx格式文件，不支持: {file_ext}")
                return None
            
            logger.info(f"读取Excel文件合并单元格信息: {file_path}, 工作表: {sheet_name}")
            
            # 使用openpyxl加载工作簿
            wb = load_workbook(file_path, read_only=False, data_only=True)
            
            # 获取指定工作表
            if isinstance(sheet_name, int):
                sheet_names = wb.sheetnames
                if sheet_name < 0 or sheet_name >= len(sheet_names):
                    logger.error(f"工作表索引超出范围: {sheet_name}")
                    return None
                sheet_name = sheet_names[sheet_name]
            
            if sheet_name not in wb.sheetnames:
                logger.error(f"工作表不存在: {sheet_name}")
                return None
            
            ws = wb[sheet_name]
            
            # 收集合并单元格信息
            merged_cells_info = []
            for merged_cell in ws.merged_cells.ranges:
                min_col, min_row, max_col, max_row = merged_cell.bounds
                # 获取合并单元格的值（左上角单元格的值）
                value = ws.cell(row=min_row, column=min_col).value
                
                # 计算是否跨越多行
                spans_multiple_rows = max_row > min_row
                
                merged_cell_info = {
                    'min_col': min_col,
                    'min_row': min_row,
                    'max_col': max_col,
                    'max_row': max_row,
                    'value': value,
                    'range': merged_cell.coord,
                    'spans_multiple_rows': spans_multiple_rows
                }
                merged_cells_info.append(merged_cell_info)
            
            wb.close()
            logger.info(f"成功识别 {len(merged_cells_info)} 个合并单元格")
            return merged_cells_info
            
        except Exception as e:
            logger.error(f"获取合并单元格信息失败: {str(e)}")
            return None
    
    def map_merged_cells_to_dataframe(self, 
                                     merged_cells_info: List[Dict[str, Any]],
                                     df: pd.DataFrame,
                                     header_row: int = 1) -> List[Dict[str, Any]]:
        """
        将Excel合并单元格信息映射到DataFrame索引
        
        Args:
            merged_cells_info (List[Dict]): 合并单元格信息列表
            df (pd.DataFrame): 对应的DataFrame
            header_row (int): Excel中的表头行号（从1开始）
            
        Returns:
            List[Dict]: 映射后的合并单元格信息，添加了DataFrame索引信息
        """
        try:
            if not merged_cells_info:
                return []
            
            mapped_cells = []
            
            # DataFrame第一行对应Excel的header_row + 1行
            for cell_info in merged_cells_info:
                if cell_info['spans_multiple_rows']:
                    # 计算在DataFrame中的起始和结束索引
                    df_start_idx = cell_info['min_row'] - (header_row + 1)
                    df_end_idx = cell_info['max_row'] - (header_row + 1)
                    
                    # 确保索引在DataFrame范围内
                    if df_start_idx >= 0 and df_end_idx < len(df):
                        mapped_info = cell_info.copy()
                        mapped_info['df_start_idx'] = df_start_idx
                        mapped_info['df_end_idx'] = df_end_idx
                        mapped_cells.append(mapped_info)
                    else:
                        logger.debug(f"合并单元格 {cell_info['range']} 超出DataFrame范围")
            
            logger.info(f"成功映射 {len(mapped_cells)} 个跨越行的合并单元格到DataFrame索引")
            return mapped_cells
            
        except Exception as e:
            logger.error(f"映射合并单元格到DataFrame失败: {str(e)}")
            return []
    
    def detect_combinatorial_packaging(self, 
                                     df: pd.DataFrame,
                                     excel_file_path: str,
                                     sheet_name: Union[str, int] = 0,
                                     header_row: int = 1) -> pd.DataFrame:
        """
        检测组合包装，基于Excel合并单元格信息
        仅考虑'包装箱方案'列的合并单元格
        
        Args:
            df (pd.DataFrame): 要处理的数据框
            excel_file_path (str): Excel文件路径
            sheet_name (str or int, optional): 工作表名称或索引
            header_row (int): Excel中的表头行号（从1开始）
            
        Returns:
            pd.DataFrame: 添加了包装类型列的数据框
        """
        try:
            # 创建数据副本
            result_df = df.copy()
            
            # 初始化包装类型列为独立包装
            if '包装类型' not in result_df.columns:
                result_df['包装类型'] = '独立包装'
            
            # 1. 获取合并单元格信息
            merged_cells_info = self.get_merged_cells_info(excel_file_path, sheet_name)
            if not merged_cells_info:
                logger.warning("未获取到合并单元格信息，将使用备选方法")
                return result_df
            
            # 2. 找出'包装箱方案'列的列号
            # 使用openpyxl获取表头信息
            wb = load_workbook(excel_file_path, read_only=False, data_only=True)
            if isinstance(sheet_name, int):
                sheet_name = wb.sheetnames[sheet_name]
            ws = wb[sheet_name]
            
            # 查找'包装箱方案'列的列号
            package_scheme_col = None
            for col in range(1, ws.max_column + 1):
                cell_value = ws.cell(row=header_row, column=col).value
                if cell_value and str(cell_value).strip() == '包装箱方案':
                    package_scheme_col = col
                    break
            
            wb.close()
            
            # 如果找不到'包装箱方案'列，返回原数据
            if package_scheme_col is None:
                logger.warning("在Excel文件中未找到'包装箱方案'列")
                return result_df
            
            logger.info(f"找到'包装箱方案'列，列号: {package_scheme_col}")
            
            # 3. 过滤出'包装箱方案'列的合并单元格
            package_scheme_merged_cells = []
            for cell_info in merged_cells_info:
                # 检查是否是'包装箱方案'列的合并单元格，并且跨越多行
                if (cell_info['min_col'] == package_scheme_col and 
                    cell_info['max_col'] == package_scheme_col and 
                    cell_info['spans_multiple_rows']):
                    package_scheme_merged_cells.append(cell_info)
            
            logger.info(f"筛选出 {len(package_scheme_merged_cells)} 个'包装箱方案'列的合并单元格")
            
            # 4. 映射到DataFrame索引
            mapped_cells = self.map_merged_cells_to_dataframe(package_scheme_merged_cells, result_df, header_row)
            
            # 5. 标记组合包装
            for cell in mapped_cells:
                # 标记跨越多行的'包装箱方案'列合并单元格为组合包装
                result_df.loc[cell['df_start_idx']:cell['df_end_idx'], '包装类型'] = '组合包装'
                logger.debug(f"标记行 {cell['df_start_idx']}-{cell['df_end_idx']} 为组合包装")
            
            logger.info(f"基于'包装箱方案'列合并单元格检测完成，共标记 {len(mapped_cells)} 个组合包装区域")
            return result_df
            
        except Exception as e:
            logger.error(f"检测组合包装失败: {str(e)}")
            return df
    
    def verify_merged_cell_consistency(self, 
                                     merged_cells_info: List[Dict[str, Any]],
                                     df: pd.DataFrame,
                                     key_columns: List[str] = None) -> List[Dict[str, Any]]:
        """
        验证合并单元格数据一致性
        
        Args:
            merged_cells_info (List[Dict]): 合并单元格信息列表
            df (pd.DataFrame): 对应的DataFrame
            key_columns (List[str]): 用于验证一致性的关键列
            
        Returns:
            List[Dict]: 一致性验证结果
        """
        try:
            if not merged_cells_info:
                return []
            
            if key_columns is None:
                key_columns = ['客户订单号', '预完工日期']
            
            # 过滤出有效的映射到DataFrame的合并单元格
            verified_results = []
            
            for cell_info in merged_cells_info:
                if 'df_start_idx' not in cell_info or 'df_end_idx' not in cell_info:
                    continue
                
                start_idx = cell_info['df_start_idx']
                end_idx = cell_info['df_end_idx']
                
                # 验证关键列数据一致性
                consistency_issues = []
                for col in key_columns:
                    if col in df.columns:
                        # 获取该范围内的数据
                        col_values = df.loc[start_idx:end_idx, col].dropna().unique()
                        if len(col_values) > 1:
                            consistency_issues.append({
                                'column': col,
                                'unique_values': list(col_values),
                                'count': len(col_values)
                            })
                
                verified_results.append({
                    'range': cell_info['range'],
                    'df_start_idx': start_idx,
                    'df_end_idx': end_idx,
                    'has_consistency_issues': len(consistency_issues) > 0,
                    'issues': consistency_issues
                })
            
            return verified_results
            
        except Exception as e:
            logger.error(f"验证合并单元格一致性失败: {str(e)}")
            return []
    
    def identify_combinatorial_packaging_with_backup(self, 
                                                   df: pd.DataFrame,
                                                   excel_file_path: str = None,
                                                   sheet_name: Union[str, int] = 0,
                                                   header_row: int = 1,
                                                   package_scheme_priority: bool = True) -> pd.DataFrame:
        """
        识别组合包装，仅使用精确的Excel合并单元格信息
        重要说明：为确保准确性，暂时禁用其他识别方法
        
        Args:
            df (pd.DataFrame): 要处理的数据框
            excel_file_path (str, optional): Excel文件路径
            sheet_name (str or int, optional): 工作表名称或索引
            header_row (int): Excel中的表头行号（从1开始）
            package_scheme_priority (bool): 是否启用包装方案优先级（有明确包装方案优先为独立包装）
            
        Returns:
            pd.DataFrame: 添加了包装类型列的数据框
        """
        try:
            # 创建数据副本
            result_df = df.copy()
            
            # 初始化包装类型列为独立包装
            if '包装类型' not in result_df.columns:
                result_df['包装类型'] = '独立包装'
            else:
                # 重置包装类型为独立包装，确保重新准确判断
                result_df['包装类型'] = '独立包装'
            
            # 只使用精确的Excel合并单元格信息，禁用其他识别方法
            if excel_file_path and os.path.exists(excel_file_path) and excel_file_path.lower().endswith('.xlsx'):
                # 精确识别组合包装，只基于'包装箱方案'列的合并单元格
                logger.info("开始基于'包装箱方案'列合并单元格的精确识别")
                result_df = self.detect_combinatorial_packaging(result_df, excel_file_path, sheet_name, header_row)
            else:
                logger.warning("Excel文件路径无效或不存在，无法进行精确的包装类型识别")
            
            logger.info(f"包装类型识别完成，共识别 {len(result_df[result_df['包装类型'] == '组合包装'])} 条组合包装记录")
            return result_df
            
        except Exception as e:
            logger.error(f"识别组合包装失败: {str(e)}")
            return df